﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ChBusiness.Common;
using System.Data.Common;
using System.Data.OracleClient;
using Common;
using WF_Business;

namespace ChBusiness.InterfaceClass
{
    /// <summary>
    /// 地信中心接口操作类
    /// </summary>
    public class DXCenterClass
    {
        OracleSystemOperation commonOperation = new OracleSystemOperation();
        Database dbTemp = BasicOperate.CreateNetOfficeDataBase();

        /// <summary>
        /// Initializes a new instance of the <see cref="DXCenterClass"/> class.
        /// </summary>
        public DXCenterClass()
        {
            //如果使用设计的组件，请取消注释以下行 
            //InitializeComponent(); 
        }

        #region 根据字段名注释更新用户信息
        /// <summary>
        /// Gets the user info.
        /// </summary>
        /// <param name="strCheckCode">The STR check code.</param>
        /// <param name="strXml">The STR XML.</param>
        /// <returns></returns>
        public string UpdateUserInfoByComment(string strCheckCode, string strXml)
        {
            bool bReturn =false;
            if (Encode.Md5(DateTime.Now.ToShortDateString()) == strCheckCode)
            {
                DataSet dsTemp = commonOperation.GetDatasetFromXml(strXml);
                DataTable dtComments= commonOperation.GetFieldsComment("SYS_USER");

                if (dsTemp.Tables.Count > 0)
                {
                    DataTable dtParamXml = dsTemp.Tables[0];
                    Database dbTemp = BasicOperate.CreateNetOfficeDataBase();
                    using (DbConnection conTemp = dbTemp.CreateConnection())
                    {
                        conTemp.Open();
                        DbTransaction transTemp = conTemp.BeginTransaction();
                        try
                        {
                            foreach (DataRow drTemp in dtParamXml.Rows)
                            {
                                string strSql = string.Empty;

                                if (drTemp["operation"].ToString() == "delete")
                                {
                                    strSql = string.Format("delete from sys_user t where userid='{0}'", 
                                        drTemp["用户名"]);
                                }
                                else if (drTemp["operation"].ToString() == "add")
                                {
                                    strSql = "insert into sys_user(";
                                    string strValue = " values(";
                                    foreach (DataColumn dcOne in dtParamXml.Columns)
                                    {
                                        DataRow[] drsGet = dtComments.Select(string.Format("comments='{0}'", dcOne.ColumnName));
                                        if (drsGet.Length > 0)
                                        {
                                            strSql += string.Format("{0},", drsGet[0]["column_name"]);
                                            strValue += string.Format("'{0}',", drTemp[dcOne.ColumnName]);
                                        }
                                    }

                                    strSql += "type) ";
                                    strValue += string.Format("'{0}')",SystemConfig.BlqxsphUserType);
                                    strSql += strValue;
                                }
                                else if (drTemp["operation"].ToString() == "update")
                                {
                                    strSql = "update sys_user set ";
                                    foreach (DataColumn dcOne in dtParamXml.Columns)
                                    {
                                        DataRow[] drsGet = dtComments.Select(string.Format("comments='{0}'", dcOne.ColumnName));
                                        if (drsGet.Length > 0)
                                        {
                                            strSql += string.Format("{0}='{1}',", drsGet[0]["column_name"], drTemp[dcOne.ColumnName]);
                                        }
                                    }

                                    strSql = strSql.Substring(0, strSql.Length - 1);
                                    strSql += string.Format(" where userid='{0}'", drTemp["用户名"]);
                                }


                                dbTemp.ExecuteNonQuery(transTemp, CommandType.Text, strSql);
                            }
                            transTemp.Commit();
                            bReturn = true;
                        }
                        catch
                        {
                            transTemp.Rollback();
                            bReturn = false ;
                        }
                        finally
                        {
                            conTemp.Close();
                        }
                    }
                }
            }

            return string.Format(@"<?xml version='1.0' encoding='utf-8' ?><UserInfoResponse>{0}</UserInfoResponse>",bReturn);
        }
        #endregion

        #region 如果获取审批结果信息失败，则更新xml至数据库中存储
        /// <summary>
        /// Updates the exception and XML.
        /// </summary>
        /// <param name="strXml">The STR XML.</param>
        /// <param name="strExMsg">The STR ex MSG.</param>
        public void UpdateExceptionAndXml(string strXml, string strExMsg, int nFlag)
        {
            string strSql = @"insert into XT_INTERFACE_RESULT(POST_XML,ERROR_INFO,FLAG,POST_FLAG)
                values(:xmldata,:errorInfo,:flag,:postflag)";

            System.Data.IDataParameter[] idp = new System.Data.OracleClient.OracleParameter[2];
            
            Database dbTemp = BasicOperate.CreateNetOfficeDataBase();
            OracleCommand cmdTemp = new OracleCommand(strSql);

            OracleParameter op1 = new OracleParameter(":xmldata", OracleType.Clob);
            op1.Value = strXml;
            OracleParameter op2 = new OracleParameter(":errorInfo", OracleType.VarChar);
            op2.Value = strExMsg;
            OracleParameter op3 = new OracleParameter(":flag", OracleType.Int32);
            op3.Value = nFlag;
            OracleParameter op4 = new OracleParameter(":postflag", OracleType.Int32);
            op4.Value = -1;

            cmdTemp.Parameters.Add(op1);
            cmdTemp.Parameters.Add(op2);
            cmdTemp.Parameters.Add(op3);
            cmdTemp.Parameters.Add(op4);
            dbTemp.ExecuteNonQuery(cmdTemp);
        }
        #endregion

        #region 更新用户信息
        /// <summary>
        /// Updates the user info by user id.
        /// </summary>
        /// <param name="strCheckCode">The STR check code.</param>
        /// <param name="strXml">The STR XML.</param>
        /// <returns></returns>
        public string UpdateUserInfoByUserId(string strCheckCode, string strXml)
        {
            bool bReturn = false;
            string strErr = string.Empty;

            if (Encode.Md5(DateTime.Now.ToShortDateString()) == strCheckCode)
            {
                DataSet dsTemp = new DataSet();
                try
                {
                    //发生错误很有可能是Xml不规则
                    dsTemp = commonOperation.GetDatasetFromXml(strXml);
                }
                catch (Exception ex)
                {
                    UpdateExceptionAndXml(strXml, ex.ToString(),1);
                }

                if (dsTemp.Tables.Count > 0)
                {
                    DataTable dtParamXml = dsTemp.Tables[0];
                    Database dbTemp = BasicOperate.CreateNetOfficeDataBase();
                    using (DbConnection conTemp = dbTemp.CreateConnection())
                    {
                        conTemp.Open();
                        DbTransaction transTemp = conTemp.BeginTransaction();
                        try
                        {
                            foreach (DataRow drTemp in dtParamXml.Rows)
                            {
                                string strSql = string.Empty;
                                if (drTemp["operation"].ToString() == "delete")
                                {
                                    strSql = string.Format("delete from sys_user t where userid='{0}'",
                                        drTemp["USERID"]);
                                }
                                else if (drTemp["operation"].ToString() == "add")
                                {
                                    strSql = "insert into sys_user(";
                                    string strValue = " values(";
                                    foreach (DataColumn dcOne in dtParamXml.Columns)
                                    {
                                        if (dcOne.ColumnName.ToLower() != "operation")
                                        {
                                            string strColName = dcOne.ColumnName;
                                            if (dcOne.ColumnName.ToLower() == "orgid")//替换单位ID 名称. addby zhongjian 20100309
                                            {
                                                strColName = "unitsid";
                                            }
                                            strSql += string.Format("{0},", strColName);
                                            strValue += string.Format("'{0}',", drTemp[dcOne.ColumnName]);
                                        }
                                    }

                                    //添加更新数据的类型
                                    strSql += "SYNC_TYPE,";
                                    strValue += "'2',";
                                    //添加是否有效标识(1:有效) //addby zhongjian 20100302
                                    strSql += "isvalid,";
                                    strValue += "'1',";
                                    //用户类型(0:个人用户;1:单位用户)
                                    strSql += "usertype,";
                                    strValue += "'1',";
                                    //添加用户权限
                                    strSql += "type) ";
                                    strValue += string.Format("'{0}')", SystemConfig.BlqxsphUserType);

                                    //合并Sql语句
                                    strSql += strValue;

                                    
                                }
                                else if (drTemp["operation"].ToString() == "update")
                                {
                                    strSql = "update sys_user set ";
                                    foreach (DataColumn dcOne in dtParamXml.Columns)
                                    {
                                        if (dcOne.ColumnName.ToLower() != "operation")
                                        {
                                            string strColName = dcOne.ColumnName;
                                            if (dcOne.ColumnName.ToLower() == "orgid")//替换单位ID 名称. addby zhongjian 20100309
                                            {
                                                strColName = "unitsid";
                                            }
                                            strSql += string.Format("{0}='{1}',", strColName, drTemp[dcOne.ColumnName]);
                                        }
                                    }
                                    strSql += "SYNC_TYPE=2";
                                    strSql += string.Format(" where userid='{0}'", drTemp["USERID"]);
                                }

                                dbTemp.ExecuteNonQuery(transTemp, CommandType.Text, strSql);
                                //当用户为添加并成功时,为该用户授予国家涉密办理事项权限 addby zhongjian 20100119
                                if (drTemp["USERID"] != null && drTemp["operation"].ToString() == "add")
                                    SetUserWorkPower(drTemp["USERID"].ToString());
                            }

                            transTemp.Commit();
                            strErr = "保存成功";
                            bReturn = true;
                        }
                        catch (Exception ex)
                        {
                            transTemp.Rollback();
                            strErr = ex.Message;
                            bReturn = false;
                        }
                        finally
                        {
                            conTemp.Close();

                            UpdateExceptionAndXml(strXml, strErr,1);
                        }
                    }
                }
            }
            else
            {
                strErr = string.Format("校验码不一致:{0},{1}", Encode.Md5(DateTime.Now.ToShortDateString()),
                    strCheckCode);
            }

            return string.Format(@"<?xml version='1.0' encoding='utf-8' ?><UserInfoResponse>{0}</UserInfoResponse>", bReturn);
        }
        #endregion

        #region 更新单位信息
        /// <summary>
        /// 更新单位信息
        /// </summary>
        /// <param name="strCheckCode">MD5(date)</param>
        /// <param name="strXml">单位信息(xml内容)</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100309-->
        public string UpdateOrgInfo(string strCheckCode, string strXml)
        {
            bool bReturn = false;
            string strErr = string.Empty;

            if (Encode.Md5(DateTime.Now.ToShortDateString()) == strCheckCode)
            {
                DataSet dsTemp = new DataSet();
                try
                {
                    //发生错误很有可能是Xml不规则
                    dsTemp = commonOperation.GetDatasetFromXml(strXml);
                }
                catch (Exception ex)
                {
                    UpdateExceptionAndXml(strXml, ex.ToString(), 1);
                }

                if (dsTemp.Tables.Count > 0)
                {
                    DataTable dtParamXml = dsTemp.Tables[0];
                    Database dbTemp = BasicOperate.CreateNetOfficeDataBase();
                    using (DbConnection conTemp = dbTemp.CreateConnection())
                    {
                        conTemp.Open();
                        DbTransaction transTemp = conTemp.BeginTransaction();
                        try
                        {
                            foreach (DataRow drTemp in dtParamXml.Rows)
                            {
                                string strSql = string.Empty;
                                if (drTemp["operation"].ToString() == "delete")
                                {
                                    strSql = string.Format("delete from sys_units t where id='{0}'",
                                        drTemp["orgid"]);
                                }
                                else if (drTemp["operation"].ToString() == "add")
                                {
                                    strSql = "insert into sys_units(";
                                    string strValue = " values(";
                                    foreach (DataColumn dcOne in dtParamXml.Columns)
                                    {
                                        if (dcOne.ColumnName.ToLower() != "operation")
                                        {
                                            string strColName = "";
                                            //对应单位表字段名称
                                            switch (dcOne.ColumnName.ToLower())
                                            {
                                                case "name"://单位名称
                                                    strColName = "UNITSNAME";
                                                    break;
                                                case "orgid"://单位ID
                                                    strColName = "ID";
                                                    break;
                                                case "tel"://单位电话
                                                    strColName = "UNITSTEL";
                                                    break;
                                                case "address"://单位地址
                                                    strColName = "UNITSADDRESS";
                                                    break;
                                                case "legalperson"://法人代表或负责人 
                                                    strColName = "LEGAL";
                                                    break;
                                                case "idcode"://组织代码 
                                                    strColName = "ORGANIZATION";
                                                    break;
                                                case "kind"://单位性质
                                                    strColName = "NATURE";
                                                    break;
                                                case "regdate"://注册日期                                            
                                                    strColName = "CREATEDATE";
                                                    strValue += string.Format("to_date('{0}','yyyy-mm-dd'),", drTemp[dcOne.ColumnName]);
                                                    break;
                                                default:
                                                    strColName = dcOne.ColumnName;
                                                    break;
                                            }
                                            if (strColName != "CREATEDATE")
                                            {
                                                strValue += string.Format("'{0}',", drTemp[dcOne.ColumnName]);
                                            }
                                            strSql += string.Format("{0},", strColName);
                                        }
                                    }

                                    strSql += "SYNC_TYPE) ";
                                    strValue += string.Format("'2')");

                                    //合并Sql语句
                                    strSql += strValue;


                                }
                                else if (drTemp["operation"].ToString() == "update")
                                {
                                    strSql = "update sys_units set ";
                                    foreach (DataColumn dcOne in dtParamXml.Columns)
                                    {
                                        if (dcOne.ColumnName.ToLower() != "operation")
                                        {
                                            string strColName = "";
                                            //对应单位表字段名称
                                            switch (dcOne.ColumnName.ToLower())
                                            {
                                                case "name"://单位名称
                                                    strColName = "UNITSNAME";
                                                    break;
                                                case "orgid"://单位ID
                                                    strColName = "ID";
                                                    break;
                                                case "tel"://单位电话
                                                    strColName = "UNITSTEL";
                                                    break;
                                                case "address"://单位地址
                                                    strColName = "UNITSADDRESS";
                                                    break;
                                                case "legalperson"://法人代表或负责人 
                                                    strColName = "LEGAL";
                                                    break;
                                                case "idcode"://组织代码 
                                                    strColName = "ORGANIZATION";
                                                    break;
                                                case "kind"://单位性质
                                                    strColName = "NATURE";
                                                    break;
                                                case "regdate"://注册日期                                            
                                                    strColName = "CREATEDATE";
                                                    strSql += string.Format("{0}=to_date('{1}','yyyy-mm-dd'),", strColName, drTemp[dcOne.ColumnName]);
                                                    break;
                                                default:
                                                    strColName = dcOne.ColumnName;
                                                    break;
                                            }
                                            if (strColName != "CREATEDATE")
                                            {
                                                strSql += string.Format("{0}='{1}',", strColName, drTemp[dcOne.ColumnName]);
                                            }
                                        }
                                    }
                                    strSql += "SYNC_TYPE=2";
                                    strSql += string.Format(" where id='{0}'", drTemp["orgid"]);
                                }

                                dbTemp.ExecuteNonQuery(transTemp, CommandType.Text, strSql);
                            }

                            transTemp.Commit();
                            strErr = "保存成功";
                            bReturn = true;
                        }
                        catch (Exception ex)
                        {
                            transTemp.Rollback();
                            strErr = ex.Message;
                            bReturn = false;
                        }
                        finally
                        {
                            conTemp.Close();
                            UpdateExceptionAndXml(strXml, strErr, 1);
                        }
                    }
                }
            }
            else
            {
                strErr = string.Format("校验码不一致:{0},{1}", Encode.Md5(DateTime.Now.ToShortDateString()),
                    strCheckCode);
            }
            return string.Format(@"<?xml version='1.0' encoding='utf-8' ?><OrgInfoResponse>{0}</OrgInfoResponse>", bReturn);
        }
        #endregion

        #region 获取审批结果信息
        /// <summary>
        /// 获取审批结果信息
        /// </summary>
        /// <param name="strStatus">The STR status.</param>
        /// <param name="strDateStart">The STR date start.</param>
        /// <param name="strDateEnd">The STR date end.</param>
        /// <param name="strCheckCode">The STR check code.</param>
        /// <returns></returns>
        /// <!--添加业务删除标识条件 and t.isdelete <>1  addby zhongjian 20091223-->
        /// <!--修改业务返回状态以及返回结果编号  update by zhongjian 20100607-->
        public string GetSpResult(string strStatus, string strDateStart, string strDateEnd, string strCheckCode)
        {
            string strErr = string.Empty;
            string strSql = string.Empty;
            Database dbTemp = BasicOperate.CreateNetOfficeDataBase();
            DbConnection conTemp = dbTemp.CreateConnection();
            conTemp.Open();
            DbTransaction transTemp = conTemp.BeginTransaction();
            string strReturn = "<?xml version='1.0' encoding='utf-8' ?><DataSet>";
            try
            {
                strSql = @"select p.关联ID ""appID"",
                            (case
                                when p.status = '1' then
                                '1'
                                when p.status = '-4' then
                                '-1'
                                else '0'
                            end) ""approveResult"",
                            (case
                                 when p.status = '1' then
                                  '国测成准[' || a.国测成准年 || ']' || a.国测成准号 || '号'
                                 when p.status = '-4' then
                                  '国测成非准[' || b.国测成非准年 || ']' || b.国测成非准号 || '号'
                                 else
                                  ''
                             end)  ""approveResultNo"",
                    to_char(trunc(t.finish_time),'yyyy-mm-dd') ""approveDate"",p.STOP_REMARK ""approveNote"", 
                t.iid from st_instance t,XT_INSTANCE_EXT p 
                UT_测绘成果准予使用决定书 a,
                UT_测绘成果不准予使用决定书    b
                where t.iid=p.iid and t.iid = a.iid
                and t.iid = b.iid and t.isdelete <>1";

                if (!string.IsNullOrEmpty(strStatus))
                {
                    strSql += string.Format(@" and (case when t.status = 2 then '1' when t.status = -2 
                    then '-1' else '0' end)='{0}'", strStatus);
                }
                if (!string.IsNullOrEmpty(strDateStart))
                {
                    strSql += string.Format(@" and t.accepted_time>=to_date('{0}','yyyy-mm-dd')", strDateStart);
                }
                if (!string.IsNullOrEmpty(strDateEnd))
                {
                    strSql += string.Format(@" and t.accepted_time<=to_date('{0}','yyyy-mm-dd')", strDateEnd);
                }

                DataTable dtReturn = dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0];

                if (dtReturn.Rows.Count > 0)
                {
                    foreach (DataRow drTemp in dtReturn.Rows)
                    {
                        strReturn += "<Record>";
                        foreach (DataColumn dcOne in dtReturn.Columns)
                        {
                            if (dcOne.ColumnName.ToLower() != "iid")
                            {
                                strReturn += string.Format("<{1}>{0}</{1}>", drTemp[dcOne.ColumnName], dcOne.ColumnName);
                            }
                            //else
                            //{
                            //    strSql = string.Format("update XT_INSTANCE_EXT t set t.IS_GET=1 where iid='{0}'", drTemp[dcOne.ColumnName]);
                            //    dbTemp.ExecuteNonQuery(transTemp, CommandType.Text, strSql);
                            //}

                        }
                        strReturn += "</Record>";
                    }
                }

                transTemp.Commit();
            }
            catch (Exception ex)
            {
                transTemp.Rollback();
                strReturn += string.Format("<exception>{0}</exception>", ex.Message);
                strErr = ex.Message;
            }
            finally
            {
                //新增接口的操作日志  addby zhongjian 20090910
                conTemp.Close();
                strReturn += "</DataSet>";
                UpdateExceptionAndXml(strReturn, strErr,1);
            }

            return strReturn;
        }
        #endregion

        #region 为用户添加涉密流程的办理权限
        /// <summary>
        /// 为地信中心用户进行国家涉密办理事项授权
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100119-->
        public void SetUserWorkPower(string strUserID)
        {
            string strErr = string.Empty;
            string strSql = string.Empty;
            string strReturn = string.Empty;
            string strFlowName = SystemConfig.InterfaceSerialName;
            Database dbTemp = BasicOperate.CreateNetOfficeDataBase();
            DbConnection conTemp = dbTemp.CreateConnection();
            conTemp.Open();
            DbTransaction transTemp = conTemp.BeginTransaction();
            try
            {
                strSql = string.Format(@"insert into SYS_USER_SERIAL(userid,Serial_Name,Notes,Is_Pass,Flowtype)
                            values ('{0}','{1}','地信中心用户直接授权','1','国家涉密基础测绘成果资料提供使用审批')", strUserID, strFlowName);
                
                dbTemp.ExecuteNonQuery(transTemp, CommandType.Text, strSql);
                strReturn = string.Format("已成功为 {0} 用户授权 {1} 的办理事项.", strUserID, strFlowName);
                strErr = strReturn;
                transTemp.Commit();
            }
            catch (Exception ex)
            {
                transTemp.Rollback();
                strReturn = string.Format("为用户授权时出错,错误语句:'{0}'", strSql);
                strErr = ex.Message;
            }
            finally
            {
                //新增接口的操作日志  addby zhongjian 20090910
                conTemp.Close();
                UpdateExceptionAndXml(strReturn, strErr,1);
            }
        }
        #endregion

        #region 获取用户信息成生XML
        /// <summary>
        /// Gets the user info XML.
        /// </summary>
        /// <returns></returns>
        /// <!--addby zhongjian 20100302-->
        public string GetUserInfoXml(string strId, string strOperation)
        {
            string strReturn = string.Empty;

            string strSql = string.Format(@"select USERID ""userID"",USERNAME ""userName"", USERPWD ""userPwd"",
                TEL ""tel"",MOBILE ""mobile"",IDTYPE ""idType"",IDNUMBER ""idNumber"",FAX ""fax"",
                EMAIL ""email"",POSTCODE ""postcode"" 
                from sys_user t  where t.id='{0}' and SYNC_TYPE=2", strId);

            DataTable dtReturn;
            dtReturn=dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0]; 

            if (dtReturn.Rows.Count > 0)
            {
                strReturn = "<?xml version='1.0' encoding='utf-8' ?><DataSet>";
                foreach (DataRow drTemp in dtReturn.Rows)
                {
                    strReturn += string.Format("<Record operation='{0}'>", strOperation);
                    foreach (DataColumn dcOne in dtReturn.Columns)
                    {
                        strReturn += string.Format("<{1}>{0}</{1}>", drTemp[dcOne.ColumnName], dcOne.ColumnName);
                    }
                    strReturn += "</Record>";
                }
                strReturn += "</DataSet>";
            }

            return strReturn;
        }
        #endregion

        #region 获取用户待办业务
        /// <summary>
        /// 获取用户待办业务
        /// </summary>
        /// <param name="strUserIP">用户IP</param>
        /// <param name="strNow">时间段</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100611-->
        public DataTable GetBussinessByUserIP(string strUserIP, string strNow)
        {
            string strSql = string.Format(@"select 案件类型, count(*) as 案件数量
                                              from sv_work_list_for_worklist_node 
                                             where userid = (select userid from st_user where userip = '{0}') ", strUserIP);
            if (!string.IsNullOrEmpty(strNow))
                strSql += string.Format(" and 接收时间 > to_date('{0}', 'yyyy-mm-dd hh24:mi:ss') ", strNow);
            strSql += " group by 案件类型 ";
            return dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0]; 
        }
        #endregion

        /// <summary>
        /// 更新涉密成果分发系统的信息
        /// </summary>
        /// <param name="AppId">用户订单业务唯一标识号</param>
        /// <param name="RsRange">成果范围（可空）</param>
        /// <param name="RsName">成果名称（可空）</param>
        /// <param name="Secret">密级（可空）</param>
        /// <param name="MapStr">地理范围展示字符串（可空）</param>
        /// <returns></returns>
        public string UpdateSmcgffInfo(string AppId, string RsRange, string RsName, string Secret, string MapStr)
        {
            string strSql = string.Format(@"insert into XT_INTERFACE_RESULT(appid,rsRange,rsName,secret,mapStr)
                values('{0}','{1}','{2}','{3}','{4}')",AppId, RsRange, RsName, Secret, MapStr);
            int nReturn = SysParams.OAConnection().RunSql(strSql);
            return nReturn == 1 ? "true" : "false";
        }

        #region 获取审批结果信息
        /// <summary>
        /// 获取审批结果信息
        /// </summary>
        /// <param name="strAppId">应用id</param>
        /// <returns></returns>
        public string GetShenQingResult(string strAppId)
        {
            string strErr = string.Empty;
            string strSql = string.Empty;
            
            string strReturn = "<?xml version='1.0' encoding='utf-8' ?><DataSet>";
            try
            {
                strSql = @"select p.关联ID ""appID"",t.iid ""ApplyNo"",a.使用目的 ""Purpose"",
                项目名称 ""ProjectName"",批准单位名称 ""ApproveDept"",
                (case
                                when 单位性质1 = '1' then
                                '国家机关'
                                when 单位性质2 = '1' then
                                '事业单位'
 when 单位性质3 = '1' then
                                '企业'
 when 单位性质4 = '1' then
                                '军队'
 when 单位性质5 = '1' then
                                '其他'
                            end) ""AppDeptType"",批准文号 ""ApproveNo"",
                            (case
                                when p.status = '1' then
                                '1'
                                when p.status = '-4' then
                                '-1'
                                else '0'
                            end) ""approveResult"",
                            成果资料名称 ""ProductName"",a.种类 ""Range"",备注 ""Memo"",
                        (case
                                when b.提供方式1 = '1' then
                                '无偿提供'
                                when 提供方式2 = '1' then
                                '有偿提供'
                            end) ""ProvideForm"",地点 ""SendDept"",
                            (case
                                 when p.status = '1' then
                                 a.JDSBH
                                 when p.status = '-4' then
                                  a.BZYSYJDSH
                                 else
                                  ''
                             end)  ""approveResultNo"",
                    to_char(trunc(t.finish_time),'yyyy-mm-dd') ""approveDate"",p.STOP_REMARK ""approveNote"" from st_instance t,XT_INSTANCE_EXT p,
                UT_测绘成果使用申请表 a,UT_测绘成果准予使用决定书 b 
                where t.iid=p.iid and t.iid = a.iid and a.iid=b.iid";

                if (!string.IsNullOrEmpty(strAppId))
                {
                    strSql += string.Format(@" and p.关联ID='{0}'", strAppId);
                }

                DataTable dtReturn = new DataTable();
                SysParams.OAConnection().RunSql(strSql,out dtReturn);

                if (dtReturn.Rows.Count > 0)
                {
                    foreach (DataRow drTemp in dtReturn.Rows)
                    {
                        strReturn += "<Record>";
                        foreach (DataColumn dcOne in dtReturn.Columns)
                        {
                            if (dcOne.ColumnName.ToLower() != "iid")
                            {
                                strReturn += string.Format("<{1}>{0}</{1}>", drTemp[dcOne.ColumnName], dcOne.ColumnName);
                            }
                            //else
                            //{
                            //    strSql = string.Format("update XT_INSTANCE_EXT t set t.IS_GET=1 where iid='{0}'", drTemp[dcOne.ColumnName]);
                            //    dbTemp.ExecuteNonQuery(transTemp, CommandType.Text, strSql);
                            //}

                        }
                        strReturn += "</Record>";
                    }
                }
            }
            catch (Exception ex)
            {
                strReturn += string.Format("<exception>{0}</exception>", ex.Message);
                strErr = ex.Message;
            }
            finally
            {
                //新增接口的操作日志
                strReturn += "</DataSet>";
                UpdateExceptionAndXml(strReturn, strErr, 1);
            }

            return strReturn;
        }
        #endregion
    }
        
}
